import openpyxl
from openpyxl.styles import Alignment, Font
from openpyxl.utils import get_column_letter
import calendar


def create_month_sheet(ws, year, month):
    # 设置列宽和行高
    for col in range(1, 10):  # A到I列
        ws.column_dimensions[get_column_letter(col)].width = 15
    for row in range(1, 40):  # 假设最多39行
        ws.row_dimensions[row].height = 20

    # 第一行合并C到I列，填写月份工作内容
    ws.merge_cells(start_row=1, start_column=3, end_row=1, end_column=9)
    month_name = calendar.month_name[month]
    ws.cell(row=1, column=3, value=f"{month_name}工作内容")
    # 设置字体加粗
    cell = ws.cell(row=1, column=3)
    cell.font = Font(bold=True)
    cell.alignment = Alignment(horizontal='center', vertical='center')

    # 第二行C到I列填写星期一到星期日
    weekdays = ['星期一', '星期二', '星期三', '星期四', '星期五', '星期六', '星期日']
    for i in range(3, 10):
        ws.cell(row=2, column=i, value=weekdays[i - 3])
        ws.cell(row=2, column=i).font = Font(bold=True)
        ws.cell(row=2, column=i).alignment = Alignment(horizontal='center', vertical='center')

    # 第三行A列填写“日期”
    ws.cell(row=3, column=1, value="日期")
    ws.cell(row=3, column=1).font = Font(bold=True)
    ws.cell(row=3, column=1).alignment = Alignment(horizontal='center', vertical='center')

    # 获取该月的日历
    cal = calendar.Calendar(firstweekday=0)  # 0 = Monday
    month_calendar = cal.monthdayscalendar(year, month)

    # 填充第三行的日期
    for week_idx, week in enumerate(month_calendar):
        for day_idx, day in enumerate(week):
            if day != 0:
                row_num = 3 + week_idx
                col_num = 3 + day_idx
                ws.cell(row=row_num, column=col_num, value=day)
                ws.cell(row=row_num, column=col_num).alignment = Alignment(horizontal='center', vertical='center')

    # 从第四行开始，每两行重复一次格式
    start_data_row = 4
    for week_idx, week in enumerate(month_calendar):
        for day_idx, day in enumerate(week):
            if day != 0:
                row_num = start_data_row + 2 * week_idx
                col_num = 1
                # 合并第一列，填写“工作内容”
                ws.merge_cells(start_row=row_num, start_column=1, end_row=row_num + 1, end_column=1)
                ws.cell(row=row_num, column=1, value="工作内容")
                ws.cell(row=row_num, column=1).alignment = Alignment(horizontal='center', vertical='center')

                # 第二列填写“工作计划”和“工作内容”
                ws.cell(row=row_num, column=2, value="工作计划")
                ws.cell(row=row_num + 1, column=2, value="工作内容")
                ws.cell(row=row_num, column=2).alignment = Alignment(horizontal='center', vertical='center')
                ws.cell(row=row_num + 1, column=2).alignment = Alignment(horizontal='center', vertical='center')

                # C到I列留空，供填写日报内容
                for i in range(3, 10):
                    ws.cell(row=row_num, column=i, value=None)
                    ws.cell(row=row_num + 1, column=i, value=None)

    # 填充备注
    ws.cell(row=33, column=1, value='1、每周五完成下一周的工作计划：')
    ws.cell(row=34, column=1, value='2、每日日报需要在每天下午下班前填写完毕：')
    # 设置字体加粗
    for row in [33, 34]:
        cell = ws.cell(row=row, column=1)
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal='left', vertical='center')

    # 填充月份列表
    months = ['1月', '2月', '3月', '4月', '5月', '6月', '7月', '8月', '9月', '10月', '11月', '12月']
    for i in range(1, 13):
        ws.cell(row=36, column=i, value=months[i - 1])
        ws.cell(row=36, column=i).font = Font(bold=True)
        ws.cell(row=36, column=i).alignment = Alignment(horizontal='center', vertical='center')


# 主程序
year = 2023  # 假设是2023年

wb = openpyxl.Workbook()
for month in range(1, 13):
    ws = wb.create_sheet(title=calendar.month_name[month])
    create_month_sheet(ws, year, month)

# 删除默认的Sheet
del wb['Sheet']

wb.save(str(year) + "" + '年-日报模板.xlsx')

print("Excel 月报模板已生成：" + str(year) + "" + '年-日报模板.xlsx')
